<!DOCTYPE html>
<html lang="zh-CN">
  <head>
    <meta charset="utf-8">
    <meta name="viewport" content="width=device-width,initial-scale=1">
    <title>Node 第 5 天 | Megasu的笔记</title>
    <meta name="generator" content="VuePress 1.5.4">
    <link rel="icon" href="/heima2020/favicon.ico">
    <meta name="description" content="Megasu的笔记整理">
    <meta name="author" content="Megasu">
    <meta name="Keywords" content="学习网站,前端,分享">
    <link rel="preload" href="/heima2020/assets/css/0.styles.aa663799.css" as="style"><link rel="preload" href="/heima2020/assets/js/app.e2adc9d2.js" as="script"><link rel="preload" href="/heima2020/assets/js/2.7389c9d3.js" as="script"><link rel="preload" href="/heima2020/assets/js/9.cd5538bb.js" as="script"><link rel="prefetch" href="/heima2020/assets/js/10.8b750e5f.js"><link rel="prefetch" href="/heima2020/assets/js/11.9224f975.js"><link rel="prefetch" href="/heima2020/assets/js/12.3ea117b6.js"><link rel="prefetch" href="/heima2020/assets/js/13.7524b74f.js"><link rel="prefetch" href="/heima2020/assets/js/14.cee0fcf8.js"><link rel="prefetch" href="/heima2020/assets/js/15.7a4a27f7.js"><link rel="prefetch" href="/heima2020/assets/js/16.947d882b.js"><link rel="prefetch" href="/heima2020/assets/js/17.79d3becb.js"><link rel="prefetch" href="/heima2020/assets/js/18.48208e3a.js"><link rel="prefetch" href="/heima2020/assets/js/19.3d0c49ef.js"><link rel="prefetch" href="/heima2020/assets/js/20.1f6114c8.js"><link rel="prefetch" href="/heima2020/assets/js/21.85e67b4a.js"><link rel="prefetch" href="/heima2020/assets/js/22.ce482f4d.js"><link rel="prefetch" href="/heima2020/assets/js/23.a150bd35.js"><link rel="prefetch" href="/heima2020/assets/js/24.8cd80d5d.js"><link rel="prefetch" href="/heima2020/assets/js/25.0001c7c1.js"><link rel="prefetch" href="/heima2020/assets/js/26.aa4d65a5.js"><link rel="prefetch" href="/heima2020/assets/js/27.baffff1d.js"><link rel="prefetch" href="/heima2020/assets/js/28.20430dcf.js"><link rel="prefetch" href="/heima2020/assets/js/29.7bbc3c04.js"><link rel="prefetch" href="/heima2020/assets/js/3.0076a912.js"><link rel="prefetch" href="/heima2020/assets/js/30.5f74696f.js"><link rel="prefetch" href="/heima2020/assets/js/31.a0bf56d6.js"><link rel="prefetch" href="/heima2020/assets/js/32.dd5a93dd.js"><link rel="prefetch" href="/heima2020/assets/js/33.92de7335.js"><link rel="prefetch" href="/heima2020/assets/js/34.bf468ed6.js"><link rel="prefetch" href="/heima2020/assets/js/35.28502be1.js"><link rel="prefetch" href="/heima2020/assets/js/36.3622a14c.js"><link rel="prefetch" href="/heima2020/assets/js/37.626e17d3.js"><link rel="prefetch" href="/heima2020/assets/js/38.860020b8.js"><link rel="prefetch" href="/heima2020/assets/js/39.c092c283.js"><link rel="prefetch" href="/heima2020/assets/js/4.586af112.js"><link rel="prefetch" href="/heima2020/assets/js/40.a6ba2743.js"><link rel="prefetch" href="/heima2020/assets/js/41.43d70bcd.js"><link rel="prefetch" href="/heima2020/assets/js/42.6486a709.js"><link rel="prefetch" href="/heima2020/assets/js/43.670b256a.js"><link rel="prefetch" href="/heima2020/assets/js/44.eb371480.js"><link rel="prefetch" href="/heima2020/assets/js/45.5b45f11e.js"><link rel="prefetch" href="/heima2020/assets/js/46.bc59be94.js"><link rel="prefetch" href="/heima2020/assets/js/47.ec36fca9.js"><link rel="prefetch" href="/heima2020/assets/js/48.51e80f8f.js"><link rel="prefetch" href="/heima2020/assets/js/5.6eb7e243.js"><link rel="prefetch" href="/heima2020/assets/js/6.1b7d5a6d.js"><link rel="prefetch" href="/heima2020/assets/js/7.f611fe3c.js"><link rel="prefetch" href="/heima2020/assets/js/8.c14cc922.js">
    <link rel="stylesheet" href="/heima2020/assets/css/0.styles.aa663799.css">
  </head>
  <body>
    <div id="app" data-server-rendered="true"><div class="theme-container"><header class="navbar"><div class="sidebar-button"><svg xmlns="http://www.w3.org/2000/svg" aria-hidden="true" role="img" viewBox="0 0 448 512" class="icon"><path fill="currentColor" d="M436 124H12c-6.627 0-12-5.373-12-12V80c0-6.627 5.373-12 12-12h424c6.627 0 12 5.373 12 12v32c0 6.627-5.373 12-12 12zm0 160H12c-6.627 0-12-5.373-12-12v-32c0-6.627 5.373-12 12-12h424c6.627 0 12 5.373 12 12v32c0 6.627-5.373 12-12 12zm0 160H12c-6.627 0-12-5.373-12-12v-32c0-6.627 5.373-12 12-12h424c6.627 0 12 5.373 12 12v32c0 6.627-5.373 12-12 12z"></path></svg></div> <a href="/heima2020/" class="home-link router-link-active"><!----> <span class="site-name">Megasu的笔记</span></a> <div class="links"><div class="search-box"><input aria-label="Search" autocomplete="off" spellcheck="false" value=""> <!----></div> <nav class="nav-links can-hide"><div class="nav-item"><a href="/heima2020/" class="nav-link">
  首页
</a></div> <!----></nav></div></header> <div class="sidebar-mask"></div> <aside class="sidebar"><nav class="nav-links"><div class="nav-item"><a href="/heima2020/" class="nav-link">
  首页
</a></div> <!----></nav>  <ul class="sidebar-links"><li><section class="sidebar-group depth-0"><a href="/heima2020/node/day01" class="sidebar-heading clickable open"><span>Node</span> <!----></a> <ul class="sidebar-links sidebar-group-items"><li><a href="/heima2020/node/day01.html" class="sidebar-link">Node 第 1 天</a></li><li><a href="/heima2020/node/day02.html" class="sidebar-link">Node 第 2 天</a></li><li><a href="/heima2020/node/day03.html" class="sidebar-link">Node 第 3 天</a></li><li><a href="/heima2020/node/day04.html" class="sidebar-link">Node 第 4 天</a></li><li><a href="/heima2020/node/day05.html" aria-current="page" class="active sidebar-link">Node 第 5 天</a><ul class="sidebar-sub-headers"><li class="sidebar-sub-header"><a href="/heima2020/node/day05.html#node-第-5-天" class="sidebar-link">Node 第 5 天</a><ul class="sidebar-sub-headers"><li class="sidebar-sub-header"><a href="/heima2020/node/day05.html#_1-学习目标" class="sidebar-link">1. 学习目标</a></li><li class="sidebar-sub-header"><a href="/heima2020/node/day05.html#_2-数据库的基本概念" class="sidebar-link">2. 数据库的基本概念</a></li><li class="sidebar-sub-header"><a href="/heima2020/node/day05.html#_3-安装-mysql" class="sidebar-link">3. 安装 MySQL</a></li><li class="sidebar-sub-header"><a href="/heima2020/node/day05.html#_4-mysql-的基本使用" class="sidebar-link">4. MySQL 的基本使用</a></li><li class="sidebar-sub-header"><a href="/heima2020/node/day05.html#_5-sql-语句" class="sidebar-link">5. SQL 语句</a></li><li class="sidebar-sub-header"><a href="/heima2020/node/day05.html#_6-mysql-模块" class="sidebar-link">6. MySQL 模块</a></li></ul></li></ul></li><li><a href="/heima2020/node/day06.html" class="sidebar-link">Node 第 6 天</a></li><li><a href="/heima2020/node/day07.html" class="sidebar-link">Node 第 7 天</a></li><li><a href="/heima2020/node/day08.html" class="sidebar-link">Node 第 8 天</a></li></ul></section></li></ul> </aside> <main class="page"> <div class="theme-default-content content__default"><h2 id="node-第-5-天"><a href="#node-第-5-天" class="header-anchor">#</a> Node 第 5 天</h2> <h3 id="_1-学习目标"><a href="#_1-学习目标" class="header-anchor">#</a> 1. 学习目标</h3> <p>◆ 能够知道如何配置 MySQL 数据库环境</p> <p>◆ 能够认识并使用常见的 SQL 语句操作数据库</p> <p>◆ 能够在项目中操作 MySQL 数据库</p> <h3 id="_2-数据库的基本概念"><a href="#_2-数据库的基本概念" class="header-anchor">#</a> 2. 数据库的基本概念</h3> <h4 id="_2-1-什么是数据库"><a href="#_2-1-什么是数据库" class="header-anchor">#</a> 2.1 什么是数据库</h4> <ol><li>数据库（database）是用来组织、存储和管理数据的仓库</li> <li>为了方便管理互联网世界中的数据，就有了数据库管理系统的概念（简称：数据库）。用户可以对数据库中的数据进行新增、查询、更新、删除等操作</li></ol> <h4 id="_2-2-常见的数据库以及分类"><a href="#_2-2-常见的数据库以及分类" class="header-anchor">#</a> 2.2 常见的数据库以及分类</h4> <ol><li>常见的数据库分类下面几种</li></ol> <ul><li><code>MySQL</code> 数据库（目前使用最广泛、流行度最高的开源免费数据库；<code>Community</code> + <code>Enterprise</code>）</li> <li><code>Oracle</code> 数据库（收费）</li> <li><code>SQL Server</code> 数据库（收费）</li> <li><code>Mongodb</code> 数据库 (<code>Community</code> + <code>Enterprise</code>)</li></ul> <ol start="2"><li>数据库分类 - 传统数据库(关系型数据库或 SQL 数据库) - MySQL、Oracle、SQL Server，这三者的设计理念相同，用法比较类似 - 新型数据库(非关系数据库或 NoSQL 数据库) - Mongodb，它在一定程度上弥补了传统型数据库的缺陷</li></ol> <h4 id="_2-3-传统型数据库的数据组织结构"><a href="#_2-3-传统型数据库的数据组织结构" class="header-anchor">#</a> 2.3 传统型数据库的数据组织结构</h4> <ol><li>在传统的类型的数据库中，数据的组织结构分为数据库(<code>database</code>)、数据表(<code>table</code>)、数据行(<code>row</code>)、字段(<code>field</code>) 这 4 大部分组成</li></ol> <img src="/heima2020/assets/img/001 - 传统型数据库中的数据组织结构.81b61633.png" style="zoom:67%;"> <ol start="2"><li>概念对象</li></ol> <ul><li><strong>数据库</strong>类似于 Excel 的<strong>工作簿</strong></li> <li><strong>数据表</strong>类似于 Excel 的<strong>工作表</strong></li> <li><strong>数据行</strong>类似于 Excel 的<strong>每一行数据</strong></li> <li><strong>字段类</strong>似于 Excel 的<strong>列</strong></li> <li>每个字段都有对应的数据类型</li></ul> <h4 id="_2-4-实际开发中库、表、行、字段的关系"><a href="#_2-4-实际开发中库、表、行、字段的关系" class="header-anchor">#</a> 2.4 实际开发中库、表、行、字段的关系</h4> <ol><li>在实际项目开发中，一般情况下，每个项目都对应独立的数据库</li> <li>不同的数据，要存储到数据库的不同表中，例如：用户数据存储到 users 表中，图书数据存储到 books 表中</li> <li>每个表中具体存储哪些信息，由字段来决定，例如：我们可以为 users 表设计 id、username、password 这 3 个字段</li> <li>表中的行，代表每一条具体的数据</li></ol> <h3 id="_3-安装-mysql"><a href="#_3-安装-mysql" class="header-anchor">#</a> 3. 安装 MySQL</h3> <h4 id="_3-1-了解需要安装哪些-mysql-相关的软件"><a href="#_3-1-了解需要安装哪些-mysql-相关的软件" class="header-anchor">#</a> 3.1 了解需要安装哪些 <code>MySQL</code> 相关的软件</h4> <ol><li>对于开发人员来说，只需要安装 <code>MySQL Server</code> 和 <code>MySQL Workbench</code> 这两个软件，就能满足开发的需要了</li></ol> <ul><li><code>MySQL Server</code>：专门用来提供数据存储和服务的软件</li> <li><code>MySQL Workbench</code>：可视化的 <code>MySQL</code> 管理工具，通过它，可以方便的操作存储在 MySQL Server 中的数据</li></ul> <h4 id="_3-2-mysql-在-mac-环境下的安装"><a href="#_3-2-mysql-在-mac-环境下的安装" class="header-anchor">#</a> 3.2 <code>MySQL</code> 在 <code>Mac</code> 环境下的安装</h4> <ol><li>在 <code>Mac</code> 环境下安装 <code>MySQL</code> 的过程比 <code>Windows</code> 环境下的步骤简单很多</li></ol> <ul><li>先运行 <code>mysql-8.0.19-macos10.15-x86_64.dmg</code>这个安装包，将 <code>MySQL Server</code> 安装到 <code>Mac</code> 系统</li> <li>再运行 <code>mysql-workbench-community-8.0.19-macos-x86_64.dmg</code> 这个安装包，将可视化的 <code>MySQL Workbench</code> 工具安装到 <code>Mac</code> 系统</li> <li>具体的安装教程，可以参考 <code>素材 -&gt; MySQL for Mac -&gt;安装教程 - Mac系统安装MySql -&gt; README.md</code></li></ul> <h4 id="_3-3-mysql-在-windows-环境下的安装"><a href="#_3-3-mysql-在-windows-环境下的安装" class="header-anchor">#</a> 3.3 <code>MySQL</code> 在 <code>Windows</code> 环境下的安装</h4> <ol><li>在 <code>Windows</code> 环境下安装 <code>MySQL</code>，只需要运行 <code>mysql-installer-community-8.0.19.0.msi</code> 这个安装包，就能一次性将 <code>MySQL Server</code>和 <code>MySQL Workbench</code> 安装到自己的电脑上</li> <li>具体的安装教程，可以参考 <code>素材 -&gt; MySQL for Windows -&gt;安装教程 - Windows系统安装MySql -&gt; README.md</code></li></ol> <h3 id="_4-mysql-的基本使用"><a href="#_4-mysql-的基本使用" class="header-anchor">#</a> 4. MySQL 的基本使用</h3> <h4 id="_4-1-使用-mysql-workbench-管理数据库"><a href="#_4-1-使用-mysql-workbench-管理数据库" class="header-anchor">#</a> 4.1 使用 <code>MySQL Workbench</code> 管理数据库</h4> <ol><li><p>点击 <code>window</code> 键，找到所有应用，找到安装的 <code>MySQL Workbench</code>，点击即可</p></li> <li><p>按照图示连接数据库</p></li></ol> <img src="/heima2020/assets/img/002 - 连接数据库.345f8be7.png" style="zoom:67%;"> <h4 id="_4-2-了解主界面的组成部分"><a href="#_4-2-了解主界面的组成部分" class="header-anchor">#</a> 4.2 了解主界面的组成部分</h4> <ol><li>从上到下，从左到右依次是</li></ol> <ul><li>菜单栏</li> <li>工具栏</li> <li>数据库列表</li> <li>代码编辑区域</li> <li>信息区域</li> <li>输出区域</li></ul> <ol start="2"><li>图示组成部分以及如何设置界面</li></ol> <img src="/heima2020/assets/img/003 - 了解主界面的组成部分.76b46560.png" style="zoom:67%;"> <h4 id="_4-3-创建数据库"><a href="#_4-3-创建数据库" class="header-anchor">#</a> 4.3 创建数据库</h4> <ol><li>创建数据库的步骤</li></ol> <ul><li><p>点击创建库的按钮</p></li> <li><p>填写数据库的名称</p></li> <li><p>点击 <code>Apply</code> 按钮，创建数据库</p></li></ul> <ol start="2"><li>创建数据库图示</li></ol> <img src="/heima2020/assets/img/004 - 创建数据库.be1cf17c.png" style="zoom:67%;"> <h4 id="_4-4-创建数据表"><a href="#_4-4-创建数据表" class="header-anchor">#</a> 4.4 创建数据表</h4> <ol><li><p>点击 <code>Table</code> 表右键，出现弹框 <code>create Table</code> 即可创建数据表</p></li> <li><p>创建数据表图示</p></li></ol> <img src="/heima2020/assets/img/005 - 创建数据库.a03c7e8f.png" style="zoom:80%;"> <h4 id="_4-5-设计表的字段名称和数据类型"><a href="#_4-5-设计表的字段名称和数据类型" class="header-anchor">#</a> 4.5 设计表的字段名称和数据类型</h4> <ol><li>根据图示设计字段名称</li></ol> <img src="/heima2020/assets/img/007 - 创建数据表.eca72db1.png" style="zoom:80%;"> <ol start="2"><li><code>DataType</code> 常见的数据类型：</li></ol> <ul><li><code>int</code> 整数</li> <li><code>varchar(len)</code> 字符串</li> <li><code>tinyint(1)</code>布尔值</li></ul> <h4 id="_4-6-设置字段的特殊标识"><a href="#_4-6-设置字段的特殊标识" class="header-anchor">#</a> 4.6 设置字段的特殊标识</h4> <ol><li>设置字段的特殊标识</li></ol> <ul><li><code>PK</code>（<code>Primary Key</code>） --- 主键、唯一标识</li> <li><code>NN</code>（<code>Not Null</code>） --- 值不允许为空</li> <li><code>UQ</code>（<code>Unique</code>） --- 值唯一</li> <li><code>AI</code>（<code>Auto Increment</code>） --- 值自动增长</li></ul> <ol start="2"><li>根据图示设置字段的特殊标识</li></ol> <img src="/heima2020/assets/img/007 - 创建数据表.eca72db1.png" style="zoom:80%;"> <h4 id="_4-7-向表中写入数据"><a href="#_4-7-向表中写入数据" class="header-anchor">#</a> 4.7 向表中写入数据</h4> <ol><li>按照图示想表中写入数据</li></ol> <img src="/heima2020/assets/img/008 - 向表中写入数据.1da39ce9.png" style="zoom:80%;"> <h3 id="_5-sql-语句"><a href="#_5-sql-语句" class="header-anchor">#</a> 5. SQL 语句</h3> <h4 id="_5-1-什么是-sql"><a href="#_5-1-什么是-sql" class="header-anchor">#</a> 5.1 什么是 SQL</h4> <ol><li><code>SQL</code> 是 <strong>结构化查询语言</strong>，专门用来<strong>访问和处理数据库的编程语言</strong>。能够让我们以编程的形式，操作数据库里面的数据</li> <li>三个关键点：
<ul><li><code>SQL</code> 是一门<strong>数据库编程语言</strong></li> <li>使用 <code>SQL</code> 语言编写出来的代码，叫做 <strong>SQL 语句</strong></li> <li><code>SQL</code> 语言只能在关系型数据库中使用（例如 <code>MySQL</code>、<code>Oracle</code>、<code>SQL Server</code>）。非关系型数据库（例如 <code>Mongodb</code>）不支持 <code>SQL</code> 语言</li></ul></li></ol> <h4 id="_5-2-sql-能做什么"><a href="#_5-2-sql-能做什么" class="header-anchor">#</a> 5.2 SQL 能做什么</h4> <ol><li>从数据库中查询数据</li> <li>向数据库中插入新的数据</li> <li>更新数据库中的数据</li> <li>从数据库删除数据</li> <li>可以创建新数据库</li> <li>可在数据库中创建新表</li> <li>可在数据库中创建存储过程、视图</li></ol> <h4 id="_5-3-sql-的-select-语句"><a href="#_5-3-sql-的-select-语句" class="header-anchor">#</a> 5.3 SQL 的 SELECT 语句</h4> <h5 id="_5-3-1-语法介绍"><a href="#_5-3-1-语法介绍" class="header-anchor">#</a> 5.3.1 语法介绍</h5> <ol><li><code>SELECT</code> 语句用于从表中查询数据。执行的结果被存储在一个结果表中（称为结果集）。语法格式如下</li></ol> <img src="/heima2020/assets/img/009 - SELECT 语句.7ef2dc74.png" style="zoom:67%;"> <ol start="2"><li><p>注意：<strong>SQL 语句中的关键字对大小写不敏感</strong>。<code>SELECT</code> 等效于 <code>select</code>，<code>FROM</code> 等效于 <code>from</code></p></li> <li><p>注意：<strong>在执行语句的时候，需要选择默认的数据库，双击选择指定的数据库即可</strong></p></li></ol> <div class="language-js extra-class"><pre class="language-js"><code>select <span class="token operator">*</span> <span class="token keyword">from</span> users <span class="token constant">LIMIT</span> <span class="token number">0</span><span class="token punctuation">,</span> <span class="token number">1000</span>	Error Code<span class="token operator">:</span> <span class="token number">1046.</span> No database selected Select the <span class="token keyword">default</span> <span class="token constant">DB</span> to be used by double<span class="token operator">-</span>clicking its name <span class="token keyword">in</span> the <span class="token constant">SCHEMAS</span> list <span class="token keyword">in</span> the sidebar<span class="token punctuation">.</span>	<span class="token number">0.000</span> sec
</code></pre></div><h5 id="_5-3-2-演示两种-select-语句"><a href="#_5-3-2-演示两种-select-语句" class="header-anchor">#</a> 5.3.2 演示两种 SELECT 语句</h5> <ol><li>查询所有列写法</li></ol> <ul><li><p>从 <code>users</code> 表中选取所有的列，可以使用符号 <code>*</code> 取代列的名称</p> <img src="/heima2020/assets/img/010 - 查询所有列写法.12f16ff7.png" style="zoom:67%;"></li> <li><p>案例代码</p> <div class="language-sql extra-class"><pre class="language-sql"><code>   <span class="token comment">-- 通过 * 把 users 表中的所有的数据查询出来</span>

   <span class="token keyword">select</span> <span class="token operator">*</span> <span class="token keyword">from</span> users
</code></pre></div></li></ul> <ol start="2"><li><code>SELECT 列名称</code> 示例</li></ol> <ul><li><p>如需获取名为 <code>username</code> 和 <code>password</code> 的列的内容（从名为 <code>users</code> 的数据库表），请使用下面的 SELECT 语句</p> <img src="/heima2020/assets/img/011 - select 列名称.f28fe6e5.png" style="zoom:67%;"></li></ul> <ul><li><p>案例代码</p> <div class="language-sql extra-class"><pre class="language-sql"><code><span class="token comment">-- 多列之间，使用英文逗号进行分隔</span>

<span class="token keyword">select</span> username<span class="token punctuation">,</span> password <span class="token keyword">from</span> users
</code></pre></div></li></ul> <h4 id="_5-4-sql-的-insert-into-语句"><a href="#_5-4-sql-的-insert-into-语句" class="header-anchor">#</a> 5.4 SQL 的 INSERT INTO 语句</h4> <ol><li>语法</li></ol> <p><code>INSERT INTO</code> 语句用于向数据表中插入新的数据行，语法格式如下:</p> <img src="/heima2020/assets/img/012 - INSERT INTO 语句.3c6d4e43.png" style="zoom:67%;"> <ol start="2"><li>案例代码</li></ol> <ul><li>注意：新的数据需要加上引号，否则会报错</li></ul> <div class="language-sql extra-class"><pre class="language-sql"><code><span class="token keyword">insert</span> <span class="token keyword">into</span> users <span class="token punctuation">(</span>username<span class="token punctuation">,</span> password<span class="token punctuation">)</span> <span class="token keyword">values</span> <span class="token punctuation">(</span><span class="token string">'mz'</span><span class="token punctuation">,</span> <span class="token string">'123456'</span><span class="token punctuation">)</span>
</code></pre></div><h4 id="_5-5-sql-的-update-语句"><a href="#_5-5-sql-的-update-语句" class="header-anchor">#</a> 5.5 SQL 的 UPDATE 语句</h4> <ol><li>语法</li></ol> <p><code>Update</code> 语句用于修改表中的数据。语法格式如下</p> <img src="/heima2020/assets/img/013 - SQL 的 UPDATE 语句.7ecf9f82.png" style="zoom:67%;"> <ol start="2"><li><p>案例代码</p> <ul><li><strong>多个被更新的列之间， 使用英文的逗号进行分隔</strong></li> <li><strong>where 后面跟着的是更新的条件</strong></li> <li><strong>注意： 初学者经常忘记提供更新的 where 条件，这样会导致整张表的数据都被更新，一定要慎重</strong></li></ul> <div class="language-sql extra-class"><pre class="language-sql"><code><span class="token keyword">update</span> users <span class="token keyword">set</span> password<span class="token operator">=</span><span class="token number">654321</span> <span class="token keyword">where</span> id<span class="token operator">=</span><span class="token number">4</span>
</code></pre></div><div class="language-sql extra-class"><pre class="language-sql"><code><span class="token keyword">update</span> users <span class="token keyword">set</span> password<span class="token operator">=</span><span class="token number">888888</span><span class="token punctuation">,</span> <span class="token keyword">status</span><span class="token operator">=</span><span class="token number">1</span> <span class="token keyword">where</span> id<span class="token operator">=</span><span class="token number">4</span>
</code></pre></div></li></ol> <h4 id="_5-6-sql-的-delete-语句"><a href="#_5-6-sql-的-delete-语句" class="header-anchor">#</a> 5.6 SQL 的 DELETE 语句</h4> <ol><li>语法</li></ol> <ul><li><p>DELETE 语句用于删除表中的行</p> <img src="/heima2020/assets/img/014 - DELETE 语句用于删除表中的行.76f235e5.png" style="zoom:67%;"></li></ul> <ol start="2"><li>案例代码</li></ol> <ul><li><strong>注意： 初学者经常忘记提供更新的 where 条件，这样会导致整张表的数据都被更新，一定要慎重</strong></li></ul> <div class="language-js extra-class"><pre class="language-js"><code><span class="token keyword">delete</span> <span class="token keyword">from</span> users where id<span class="token operator">=</span><span class="token number">4</span>
</code></pre></div><h4 id="_5-7-where-子句"><a href="#_5-7-where-子句" class="header-anchor">#</a> 5.7 WHERE 子句</h4> <ol><li><code>WHERE</code> 子句用于限定选择的标准。在 <code>SELECT</code>、<code>UPDATE</code>、<code>DELETE</code> 语句中，皆可使用 <code>WHERE</code> 子句来限定选择的标准</li></ol> <img src="/heima2020/assets/img/015 - WHERE 子句.42e95392.png" style="zoom:80%;"> <ol start="2"><li>可在 <code>WHERE</code> 子句中使用的运算符</li></ol> <ul><li><p>注意：在某些版本的 <code>SQL</code> 中，操作符 <code>&lt;&gt;</code> 可以写为 <code>!=</code></p> <img src="/heima2020/assets/img/016 - WHERE 子句.9c8cda49.png" style="zoom:80%;"></li></ul> <ol start="3"><li>可以通过 <code>WHERE</code> 子句来限定 <code>SELECT</code> 的查询条件</li></ol> <div class="language-sql extra-class"><pre class="language-sql"><code><span class="token comment">-- 查询 id 为 1 的所有用户</span>
<span class="token keyword">select</span> <span class="token operator">*</span> <span class="token keyword">from</span> users <span class="token keyword">where</span> id<span class="token operator">=</span><span class="token number">1</span>

<span class="token comment">-- 查询 id 大于 2 的所有用户</span>
<span class="token keyword">select</span> <span class="token operator">*</span> <span class="token keyword">from</span> users <span class="token keyword">where</span> id<span class="token operator">&gt;</span><span class="token number">2</span>

<span class="token comment">--查询 username 不等于 admin 的所有用户</span>
<span class="token keyword">select</span> <span class="token operator">*</span> <span class="token keyword">from</span> users <span class="token keyword">where</span> username<span class="token operator">&lt;&gt;</span><span class="token string">'zs'</span>
</code></pre></div><h4 id="_5-8-sql-的-and-和-or-运算符"><a href="#_5-8-sql-的-and-和-or-运算符" class="header-anchor">#</a> 5.8 SQL 的 AND 和 OR 运算符</h4> <ol><li><p><code>AND</code> 和 <code>OR</code> 可在 <code>WHERE</code> 子语句中把两个或多个条件结合起来</p></li> <li><p><code>AND</code> 表示必须同时满足多个条件，相当于 <code>JavaScript</code> 中的 <code>&amp;&amp;</code> 运算符，例如 <code>if (a !== 10 &amp;&amp; a !== 20)</code></p></li> <li><p><code>OR</code> 表示只要满足任意一个条件即可，相当于 <code>JavaScript</code> 中的 <code>||</code> 运算符，例如 <code>if(a !== 10 || a !== 20)</code></p></li> <li><p>案例代码</p></li></ol> <div class="language-sql extra-class"><pre class="language-sql"><code><span class="token comment">-- and</span>
<span class="token comment">-- 使用 AND 来显示所有 status 为 0，并且 id 小于 3 的用户</span>

<span class="token keyword">select</span> <span class="token operator">*</span> <span class="token keyword">from</span> users <span class="token keyword">where</span> <span class="token keyword">status</span><span class="token operator">=</span><span class="token number">0</span> <span class="token operator">and</span> id<span class="token operator">&lt;</span><span class="token number">3</span>
</code></pre></div><div class="language-sql extra-class"><pre class="language-sql"><code><span class="token comment">-- or</span>
<span class="token comment">-- 使用 OR 来显示所有 status 为 1，或者 username 为 zs 的用户</span>

<span class="token keyword">select</span> <span class="token operator">*</span> <span class="token keyword">from</span> users <span class="token keyword">where</span> <span class="token keyword">status</span><span class="token operator">=</span><span class="token number">0</span> <span class="token operator">or</span> username<span class="token operator">=</span><span class="token string">'zs'</span>
</code></pre></div><h4 id="_5-9-sql-的-order-by-子句"><a href="#_5-9-sql-的-order-by-子句" class="header-anchor">#</a> 5.9 SQL 的 ORDER BY 子句</h4> <ol><li>语法</li></ol> <ul><li><code>ORDER BY</code> 语句用于根据指定的列对结果集进行排序</li> <li><code>ORDER BY</code> 语句默认按照升序对记录进行排序，<code>ASC</code> 关键字代表升序排序</li> <li>如果您希望按照降序对记录进行排序，可以使用 <code>DESC</code> 关键字</li></ul> <ol start="2"><li><code>ORDER BY</code> 子句 - 升序排序</li></ol> <p>对 <code>users</code> 表中的数据，按照 <code>status</code> 字段进行升序排序</p> <div class="language-sql extra-class"><pre class="language-sql"><code><span class="token comment">-- 下面这两条 SQL 语句是等价的，</span>
<span class="token comment">-- 因为 order by 默认进行升序排序</span>
<span class="token comment">-- 其中，ASC 关键字代表升序排序</span>

<span class="token comment">-- select * from users order by status</span>
<span class="token keyword">select</span> <span class="token operator">*</span> <span class="token keyword">from</span> users <span class="token keyword">order</span> <span class="token keyword">by</span> <span class="token keyword">status</span> <span class="token keyword">asc</span>
</code></pre></div><ol start="3"><li><code>ORDER BY</code> 子句 – 降序排序</li></ol> <p>对 users 表中的数据，按照 id 字段进行降序排序</p> <div class="language-sql extra-class"><pre class="language-sql"><code><span class="token comment">-- desc 代表降序排序</span>

<span class="token keyword">select</span> <span class="token operator">*</span> <span class="token keyword">from</span> users <span class="token keyword">order</span> <span class="token keyword">by</span> <span class="token keyword">status</span> <span class="token keyword">desc</span>
</code></pre></div><h4 id="_5-10-order-by-子句-多重排序"><a href="#_5-10-order-by-子句-多重排序" class="header-anchor">#</a> 5.10 ORDER BY 子句 – 多重排序</h4> <ol><li><p>对 <code>users</code> 表中的数据，先按照 <code>status</code> 字段进行降序排序，再按照 <code>username</code> 的字母顺序，进行升序排序</p></li> <li><p>案例代码</p></li></ol> <div class="language-sql extra-class"><pre class="language-sql"><code><span class="token keyword">select</span> <span class="token operator">*</span> <span class="token keyword">from</span> users <span class="token keyword">order</span> <span class="token keyword">by</span> <span class="token keyword">status</span> <span class="token keyword">desc</span><span class="token punctuation">,</span> username <span class="token keyword">asc</span>
</code></pre></div><h4 id="_5-11-sql-的-count-函数"><a href="#_5-11-sql-的-count-函数" class="header-anchor">#</a> 5.11 SQL 的 COUNT(*) 函数</h4> <ol><li><code>COUNT(*)</code> 函数用于返回查询结果的总数据条数</li></ol> <img src="/heima2020/assets/img/017-count.49438369.png" style="zoom:67%;"> <ol start="2"><li>案例语法</li></ol> <div class="language-sql extra-class"><pre class="language-sql"><code><span class="token keyword">select</span> <span class="token function">count</span><span class="token punctuation">(</span><span class="token operator">*</span><span class="token punctuation">)</span> <span class="token keyword">from</span> users
</code></pre></div><div class="language-sql extra-class"><pre class="language-sql"><code><span class="token comment">-- 查询 users 表中 status 为 0 的总数据条数</span>

<span class="token keyword">select</span> <span class="token function">count</span><span class="token punctuation">(</span><span class="token operator">*</span><span class="token punctuation">)</span> <span class="token keyword">from</span> users wheres <span class="token keyword">status</span><span class="token operator">=</span><span class="token number">0</span>
</code></pre></div><h4 id="_5-12-使用-as-为列设置别名"><a href="#_5-12-使用-as-为列设置别名" class="header-anchor">#</a> 5.12 使用 AS 为列设置别名</h4> <p>如果希望给查询出来的列名称设置别名，可以使用 <code>AS</code> 关键字</p> <div class="language-sql extra-class"><pre class="language-sql"><code><span class="token comment">-- 将列名从 count(*) 修改为 total</span>

<span class="token keyword">select</span> <span class="token function">count</span><span class="token punctuation">(</span><span class="token operator">*</span><span class="token punctuation">)</span> <span class="token keyword">as</span> total <span class="token keyword">from</span> users <span class="token keyword">where</span> <span class="token keyword">status</span><span class="token operator">=</span><span class="token number">0</span>
</code></pre></div><div class="language-sql extra-class"><pre class="language-sql"><code><span class="token comment">-- 将列名 username 改为 uname， password 改为 upwd</span>

<span class="token keyword">select</span> username <span class="token keyword">as</span> uname<span class="token punctuation">,</span> password <span class="token keyword">as</span> upwd <span class="token keyword">from</span> users
</code></pre></div><h4 id="_5-13-保存和打开-sql-文件"><a href="#_5-13-保存和打开-sql-文件" class="header-anchor">#</a> 5.13 保存和打开 sql 文件</h4> <ol><li>保存 <code>sql</code>文件</li></ol> <img src="/heima2020/assets/img/018 - 保存sql文件.c0d1b66a.png" style="zoom:67%;"> <ol start="2"><li>打开 <code>sql</code> 文件</li></ol> <img src="/heima2020/assets/img/019- 打开sql文件.ca926115.png" style="zoom:67%;"> <h3 id="_6-mysql-模块"><a href="#_6-mysql-模块" class="header-anchor">#</a> 6. MySQL 模块</h3> <h4 id="_6-1-在项目中操作数据库的步骤"><a href="#_6-1-在项目中操作数据库的步骤" class="header-anchor">#</a> 6.1 在项目中操作数据库的步骤</h4> <ol><li><p>安装操作 <code>MySQL</code> 数据库的第三方模块（<code>mysql</code>）</p></li> <li><p>通过 <code>mysql</code> 模块连接到 <code>MySQL</code> 数据库</p></li> <li><p>通过 <code>mysql</code> 模块执行 <code>SQL</code> 语句</p> <img src="/heima2020/assets/img/020 - 在项目中操作数据库的步骤.058632f0.png" style="zoom:67%;"></li></ol> <h4 id="_6-2-安装与配置-mysql-模块"><a href="#_6-2-安装与配置-mysql-模块" class="header-anchor">#</a> 6.2 安装与配置 mysql 模块</h4> <h5 id="_6-2-1-安装-mysql-模块"><a href="#_6-2-1-安装-mysql-模块" class="header-anchor">#</a> 6.2.1 安装 mysql 模块</h5> <p><code>mysql</code> 模块是托管于 <code>npm</code> 上的第三方模块。它提供了在 <code>Node.js</code> 项目中连接和操作 <code>MySQL</code> 数据库的能力。想要在项目中使用它，需要先运行如下命令，将 <code>mysql</code> 安装为项目的依赖包</p> <div class="language-js extra-class"><pre class="language-js"><code>cnpm i mysql <span class="token operator">-</span><span class="token constant">S</span>
</code></pre></div><h5 id="_6-2-2-配置-mysql-模块"><a href="#_6-2-2-配置-mysql-模块" class="header-anchor">#</a> 6.2.2 配置 mysql 模块</h5> <p>在使用 <code>mysql</code> 模块操作 <code>MySQL</code> 数据库之前，必须<strong>先对 <code>mysql</code>模块进行必要的配置</strong>，主要的配置步骤如下</p> <div class="language-js extra-class"><pre class="language-js"><code><span class="token comment">// 1. 导入 mysql 模块</span>
<span class="token keyword">var</span> mysql <span class="token operator">=</span> <span class="token function">require</span><span class="token punctuation">(</span><span class="token string">&quot;mysql&quot;</span><span class="token punctuation">)</span><span class="token punctuation">;</span>

<span class="token comment">// 2. 建立与 mysql 数据库的连接</span>
<span class="token keyword">var</span> db <span class="token operator">=</span> mysql<span class="token punctuation">.</span><span class="token function">createPool</span><span class="token punctuation">(</span><span class="token punctuation">{</span>
  host<span class="token operator">:</span> <span class="token string">&quot;127.0.0.1&quot;</span><span class="token punctuation">,</span> <span class="token comment">// 数据库的 ip 地址</span>
  user<span class="token operator">:</span> <span class="token string">&quot;root&quot;</span><span class="token punctuation">,</span> <span class="token comment">// 登录数据库的账号</span>
  password<span class="token operator">:</span> <span class="token string">&quot;toor&quot;</span><span class="token punctuation">,</span> <span class="token comment">// 登录数据库的密码</span>
  database<span class="token operator">:</span> <span class="token string">&quot;my_db_01&quot;</span><span class="token punctuation">,</span> <span class="token comment">// 指定要操作哪个数据库</span>
<span class="token punctuation">}</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
</code></pre></div><h4 id="_6-3-测试-mysql-模块能否正常工作"><a href="#_6-3-测试-mysql-模块能否正常工作" class="header-anchor">#</a> 6.3 测试 mysql 模块能否正常工作</h4> <ol><li><p>调用 <code>db.query()</code> 函数，指定要执行的 <code>SQL</code> 语句，通过回调函数拿到执行的结果</p></li> <li><p>图示</p></li></ol> <img src="/heima2020/assets/img/021-测试 mysql 模块能否正常工作.769a4d55.png" style="zoom:67%;"> <ol start="3"><li>案例代码</li></ol> <div class="language-js extra-class"><pre class="language-js"><code><span class="token comment">// 1. 导入 mysql 模块</span>
<span class="token keyword">var</span> mysql <span class="token operator">=</span> <span class="token function">require</span><span class="token punctuation">(</span><span class="token string">&quot;mysql&quot;</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token comment">// 2. 建立与 mysql 数据库的连接</span>
<span class="token keyword">var</span> db <span class="token operator">=</span> mysql<span class="token punctuation">.</span><span class="token function">createPool</span><span class="token punctuation">(</span><span class="token punctuation">{</span>
  host<span class="token operator">:</span> <span class="token string">&quot;127.0.0.1&quot;</span><span class="token punctuation">,</span> <span class="token comment">// 数据库的 ip 地址</span>
  user<span class="token operator">:</span> <span class="token string">&quot;root&quot;</span><span class="token punctuation">,</span> <span class="token comment">// 登录数据库的账号</span>
  password<span class="token operator">:</span> <span class="token string">&quot;toor&quot;</span><span class="token punctuation">,</span> <span class="token comment">// 登录数据库的密码</span>
  database<span class="token operator">:</span> <span class="token string">&quot;my_db_01&quot;</span><span class="token punctuation">,</span> <span class="token comment">// 指定要操作哪个数据库</span>
<span class="token punctuation">}</span><span class="token punctuation">)</span><span class="token punctuation">;</span>

<span class="token comment">// 测试 mysql 模块能够正常运行</span>
db<span class="token punctuation">.</span><span class="token function">query</span><span class="token punctuation">(</span><span class="token string">&quot;select 1&quot;</span><span class="token punctuation">,</span> <span class="token punctuation">(</span><span class="token parameter">err<span class="token punctuation">,</span> results</span><span class="token punctuation">)</span> <span class="token operator">=&gt;</span> <span class="token punctuation">{</span>
  <span class="token comment">// mysql 模块如果错误，err 中会返回错误信息</span>
  <span class="token keyword">if</span> <span class="token punctuation">(</span>err<span class="token punctuation">)</span> <span class="token keyword">return</span> console<span class="token punctuation">.</span><span class="token function">log</span><span class="token punctuation">(</span>err<span class="token punctuation">.</span>message<span class="token punctuation">)</span><span class="token punctuation">;</span>

  <span class="token comment">// 能够成功的执行 SQL 语句</span>
  console<span class="token punctuation">.</span><span class="token function">log</span><span class="token punctuation">(</span>results<span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token punctuation">}</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
</code></pre></div><h4 id="_6-4-使用-select-语句查询数据"><a href="#_6-4-使用-select-语句查询数据" class="header-anchor">#</a> 6.4 使用 select 语句查询数据</h4> <ol><li><p>需求：查询 users 表中所有的数据</p></li> <li><p>案例代码</p></li></ol> <div class="language-js extra-class"><pre class="language-js"><code><span class="token comment">// 1. 导入 mysql 模块</span>
<span class="token keyword">var</span> mysql <span class="token operator">=</span> <span class="token function">require</span><span class="token punctuation">(</span><span class="token string">&quot;mysql&quot;</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token comment">// 2. 建立与 mysql 数据库的连接</span>
<span class="token keyword">var</span> db <span class="token operator">=</span> mysql<span class="token punctuation">.</span><span class="token function">createPool</span><span class="token punctuation">(</span><span class="token punctuation">{</span>
  host<span class="token operator">:</span> <span class="token string">&quot;127.0.0.1&quot;</span><span class="token punctuation">,</span> <span class="token comment">// 数据库的 ip 地址</span>
  user<span class="token operator">:</span> <span class="token string">&quot;root&quot;</span><span class="token punctuation">,</span> <span class="token comment">// 登录数据库的账号</span>
  password<span class="token operator">:</span> <span class="token string">&quot;toor&quot;</span><span class="token punctuation">,</span> <span class="token comment">// 登录数据库的密码</span>
  database<span class="token operator">:</span> <span class="token string">&quot;my_db_01&quot;</span><span class="token punctuation">,</span> <span class="token comment">// 指定要操作哪个数据库</span>
<span class="token punctuation">}</span><span class="token punctuation">)</span><span class="token punctuation">;</span>

<span class="token comment">// 3. 查询 users 表中所有的用户数据</span>
db<span class="token punctuation">.</span><span class="token function">query</span><span class="token punctuation">(</span><span class="token string">&quot;select * from users&quot;</span><span class="token punctuation">,</span> <span class="token punctuation">(</span><span class="token parameter">err<span class="token punctuation">,</span> results</span><span class="token punctuation">)</span> <span class="token operator">=&gt;</span> <span class="token punctuation">{</span>
  <span class="token comment">// 查询失败</span>
  <span class="token keyword">if</span> <span class="token punctuation">(</span>err<span class="token punctuation">)</span> <span class="token keyword">return</span> console<span class="token punctuation">.</span><span class="token function">log</span><span class="token punctuation">(</span>err<span class="token punctuation">.</span>message<span class="token punctuation">)</span><span class="token punctuation">;</span>

  <span class="token comment">// 查询成功</span>
  console<span class="token punctuation">.</span><span class="token function">log</span><span class="token punctuation">(</span>results<span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token punctuation">}</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
</code></pre></div><h4 id="_6-5-使用-insert-into-语句插入数据"><a href="#_6-5-使用-insert-into-语句插入数据" class="header-anchor">#</a> 6.5 使用 insert into 语句插入数据</h4> <ol><li><p>需求： 向 <code>users</code> 表中新增数据， 其中 <code>username</code> 为 <code>Spider-Man</code>，<code>password</code> 为 <code>pcc321</code></p></li> <li><p>案例代码</p></li></ol> <div class="language-js extra-class"><pre class="language-js"><code><span class="token comment">// 1. 导入 mysql 模块</span>
<span class="token keyword">var</span> mysql <span class="token operator">=</span> <span class="token function">require</span><span class="token punctuation">(</span><span class="token string">&quot;mysql&quot;</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token comment">// 2. 建立与 mysql 数据库的连接</span>
<span class="token keyword">var</span> db <span class="token operator">=</span> mysql<span class="token punctuation">.</span><span class="token function">createPool</span><span class="token punctuation">(</span><span class="token punctuation">{</span>
  host<span class="token operator">:</span> <span class="token string">&quot;127.0.0.1&quot;</span><span class="token punctuation">,</span> <span class="token comment">// 数据库的 ip 地址</span>
  user<span class="token operator">:</span> <span class="token string">&quot;root&quot;</span><span class="token punctuation">,</span> <span class="token comment">// 登录数据库的账号</span>
  password<span class="token operator">:</span> <span class="token string">&quot;toor&quot;</span><span class="token punctuation">,</span> <span class="token comment">// 登录数据库的密码</span>
  database<span class="token operator">:</span> <span class="token string">&quot;my_db_01&quot;</span><span class="token punctuation">,</span> <span class="token comment">// 指定要操作哪个数据库</span>
<span class="token punctuation">}</span><span class="token punctuation">)</span><span class="token punctuation">;</span>

<span class="token comment">// 3. 声明要插入到 users 表中的数据对象</span>
<span class="token keyword">const</span> user <span class="token operator">=</span> <span class="token punctuation">{</span> username<span class="token operator">:</span> <span class="token string">&quot;tom&quot;</span><span class="token punctuation">,</span> password<span class="token operator">:</span> <span class="token string">&quot;123456&quot;</span> <span class="token punctuation">}</span><span class="token punctuation">;</span>
<span class="token comment">// 4. 待执行的 sql 语句，其中英文的 ? 表示占位符</span>
<span class="token keyword">const</span> sqlstr <span class="token operator">=</span> <span class="token string">&quot;insert into users (username, password) values (?, ?)&quot;</span><span class="token punctuation">;</span>

<span class="token comment">// 5. 使用数组的形式，依次为 ？ 占位符指定具体的值</span>
db<span class="token punctuation">.</span><span class="token function">query</span><span class="token punctuation">(</span>sqlstr<span class="token punctuation">,</span> <span class="token punctuation">[</span>user<span class="token punctuation">.</span>username<span class="token punctuation">,</span> user<span class="token punctuation">.</span>password<span class="token punctuation">]</span><span class="token punctuation">,</span> <span class="token punctuation">(</span><span class="token parameter">err<span class="token punctuation">,</span> results</span><span class="token punctuation">)</span> <span class="token operator">=&gt;</span> <span class="token punctuation">{</span>
  <span class="token comment">// 插入失败</span>
  <span class="token keyword">if</span> <span class="token punctuation">(</span>err<span class="token punctuation">)</span> <span class="token keyword">return</span> console<span class="token punctuation">.</span><span class="token function">log</span><span class="token punctuation">(</span>err<span class="token punctuation">.</span>message<span class="token punctuation">)</span><span class="token punctuation">;</span>

  <span class="token comment">// 插入成功</span>
  <span class="token keyword">if</span> <span class="token punctuation">(</span>results<span class="token punctuation">.</span>affectedRows <span class="token operator">===</span> <span class="token number">1</span><span class="token punctuation">)</span> <span class="token punctuation">{</span>
    console<span class="token punctuation">.</span><span class="token function">log</span><span class="token punctuation">(</span><span class="token string">&quot;插入数据成功&quot;</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
  <span class="token punctuation">}</span>
<span class="token punctuation">}</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
</code></pre></div><h4 id="_6-6-id-的唯一性"><a href="#_6-6-id-的唯一性" class="header-anchor">#</a> 6.6 id 的唯一性</h4> <ol><li>插入数据后，发现数据的 <code>id</code> 存在间隔</li> <li>之前存在，后来被删除了，那么在新增数据以后，即使这个 <code>id</code> 被删了，这个 <code>id</code> 也不会被占用</li></ol> <h4 id="_6-7-插入数据的便捷方式"><a href="#_6-7-插入数据的便捷方式" class="header-anchor">#</a> 6.7 插入数据的便捷方式</h4> <ol><li><p>向表中新增数据时，如果数据对象的每个属性和数据表的字段一一对应，则可以通过如下方式快速插入数据</p></li> <li><p>案例代码</p></li></ol> <div class="language-js extra-class"><pre class="language-js"><code><span class="token comment">// 1. 导入 mysql 模块</span>
<span class="token keyword">var</span> mysql <span class="token operator">=</span> <span class="token function">require</span><span class="token punctuation">(</span><span class="token string">&quot;mysql&quot;</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token comment">// 2. 建立与 mysql 数据库的连接</span>
<span class="token keyword">var</span> db <span class="token operator">=</span> mysql<span class="token punctuation">.</span><span class="token function">createPool</span><span class="token punctuation">(</span><span class="token punctuation">{</span>
  host<span class="token operator">:</span> <span class="token string">&quot;127.0.0.1&quot;</span><span class="token punctuation">,</span> <span class="token comment">// 数据库的 ip 地址</span>
  user<span class="token operator">:</span> <span class="token string">&quot;root&quot;</span><span class="token punctuation">,</span> <span class="token comment">// 登录数据库的账号</span>
  password<span class="token operator">:</span> <span class="token string">&quot;toor&quot;</span><span class="token punctuation">,</span> <span class="token comment">// 登录数据库的密码</span>
  database<span class="token operator">:</span> <span class="token string">&quot;my_db_01&quot;</span><span class="token punctuation">,</span> <span class="token comment">// 指定要操作哪个数据库</span>
<span class="token punctuation">}</span><span class="token punctuation">)</span><span class="token punctuation">;</span>

<span class="token comment">// 3. 声明要插入到 users 表中的数据对象</span>
<span class="token keyword">const</span> user <span class="token operator">=</span> <span class="token punctuation">{</span> username<span class="token operator">:</span> <span class="token string">&quot;jerry&quot;</span><span class="token punctuation">,</span> password<span class="token operator">:</span> <span class="token string">&quot;123456&quot;</span> <span class="token punctuation">}</span><span class="token punctuation">;</span>
<span class="token comment">// 4. 待执行的 sql 语句，其中英文的 ? 表示占位符</span>
<span class="token keyword">const</span> sqlstr <span class="token operator">=</span> <span class="token string">&quot;insert into users set ?&quot;</span><span class="token punctuation">;</span>

<span class="token comment">// 5. 使用数组的形式，依次为 ？ 占位符指定具体的值</span>
db<span class="token punctuation">.</span><span class="token function">query</span><span class="token punctuation">(</span>sqlstr<span class="token punctuation">,</span> user<span class="token punctuation">,</span> <span class="token punctuation">(</span><span class="token parameter">err<span class="token punctuation">,</span> results</span><span class="token punctuation">)</span> <span class="token operator">=&gt;</span> <span class="token punctuation">{</span>
  <span class="token comment">// 插入失败</span>
  <span class="token keyword">if</span> <span class="token punctuation">(</span>err<span class="token punctuation">)</span> <span class="token keyword">return</span> console<span class="token punctuation">.</span><span class="token function">log</span><span class="token punctuation">(</span>err<span class="token punctuation">.</span>message<span class="token punctuation">)</span><span class="token punctuation">;</span>

  <span class="token comment">// 插入成功</span>
  <span class="token keyword">if</span> <span class="token punctuation">(</span>results<span class="token punctuation">.</span>affectedRows <span class="token operator">===</span> <span class="token number">1</span><span class="token punctuation">)</span> <span class="token punctuation">{</span>
    console<span class="token punctuation">.</span><span class="token function">log</span><span class="token punctuation">(</span><span class="token string">&quot;插入数据成功&quot;</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
  <span class="token punctuation">}</span>
<span class="token punctuation">}</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
</code></pre></div><h4 id="_6-8-使用-update-语句更新数据"><a href="#_6-8-使用-update-语句更新数据" class="header-anchor">#</a> 6.8 使用 update 语句更新数据</h4> <ol><li><p>需求：更新表中的数据</p></li> <li><p>案例代码</p></li></ol> <div class="language-js extra-class"><pre class="language-js"><code><span class="token comment">// 1. 导入 mysql 模块</span>
<span class="token keyword">var</span> mysql <span class="token operator">=</span> <span class="token function">require</span><span class="token punctuation">(</span><span class="token string">&quot;mysql&quot;</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token comment">// 2. 建立与 mysql 数据库的连接</span>
<span class="token keyword">var</span> db <span class="token operator">=</span> mysql<span class="token punctuation">.</span><span class="token function">createPool</span><span class="token punctuation">(</span><span class="token punctuation">{</span>
  host<span class="token operator">:</span> <span class="token string">&quot;127.0.0.1&quot;</span><span class="token punctuation">,</span> <span class="token comment">// 数据库的 ip 地址</span>
  user<span class="token operator">:</span> <span class="token string">&quot;root&quot;</span><span class="token punctuation">,</span> <span class="token comment">// 登录数据库的账号</span>
  password<span class="token operator">:</span> <span class="token string">&quot;toor&quot;</span><span class="token punctuation">,</span> <span class="token comment">// 登录数据库的密码</span>
  database<span class="token operator">:</span> <span class="token string">&quot;my_db_01&quot;</span><span class="token punctuation">,</span> <span class="token comment">// 指定要操作哪个数据库</span>
<span class="token punctuation">}</span><span class="token punctuation">)</span><span class="token punctuation">;</span>

<span class="token comment">// 3. 声明要插入到 users 表中的数据对象</span>
<span class="token keyword">const</span> user <span class="token operator">=</span> <span class="token punctuation">{</span> id<span class="token operator">:</span> <span class="token number">7</span><span class="token punctuation">,</span> username<span class="token operator">:</span> <span class="token string">&quot;spike&quot;</span><span class="token punctuation">,</span> password<span class="token operator">:</span> <span class="token string">&quot;123000&quot;</span> <span class="token punctuation">}</span><span class="token punctuation">;</span>
<span class="token comment">// 4. 待执行的 sql 语句</span>
<span class="token keyword">const</span> sqlstr <span class="token operator">=</span> <span class="token string">&quot;update users set username=?, password=? where id=?&quot;</span><span class="token punctuation">;</span>

<span class="token comment">// 5. 调用 db.query() 执行 SQL 语句的同时，使用数组依次为占位符指定具体的值</span>
db<span class="token punctuation">.</span><span class="token function">query</span><span class="token punctuation">(</span>sqlstr<span class="token punctuation">,</span> <span class="token punctuation">[</span>user<span class="token punctuation">.</span>username<span class="token punctuation">,</span> user<span class="token punctuation">.</span>password<span class="token punctuation">,</span> user<span class="token punctuation">.</span>id<span class="token punctuation">]</span><span class="token punctuation">,</span> <span class="token punctuation">(</span><span class="token parameter">err<span class="token punctuation">,</span> results</span><span class="token punctuation">)</span> <span class="token operator">=&gt;</span> <span class="token punctuation">{</span>
  <span class="token comment">// 更新失败</span>
  <span class="token keyword">if</span> <span class="token punctuation">(</span>err<span class="token punctuation">)</span> <span class="token keyword">return</span> console<span class="token punctuation">.</span><span class="token function">log</span><span class="token punctuation">(</span>err<span class="token punctuation">.</span>message<span class="token punctuation">)</span><span class="token punctuation">;</span>

  <span class="token comment">// 更新成功</span>
  <span class="token keyword">if</span> <span class="token punctuation">(</span>results<span class="token punctuation">.</span>affectedRows <span class="token operator">===</span> <span class="token number">1</span><span class="token punctuation">)</span> <span class="token punctuation">{</span>
    console<span class="token punctuation">.</span><span class="token function">log</span><span class="token punctuation">(</span><span class="token string">&quot;更新数据成功&quot;</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
  <span class="token punctuation">}</span>
<span class="token punctuation">}</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
</code></pre></div><h4 id="_6-9-更新数据的便捷方式"><a href="#_6-9-更新数据的便捷方式" class="header-anchor">#</a> 6.9 更新数据的便捷方式</h4> <ol><li><p>更新表数据时，如果数据对象的每个属性和数据表的字段一一对应，则可以通过如下方式快速更新表数据</p></li> <li><p>案例代码</p></li></ol> <div class="language-js extra-class"><pre class="language-js"><code><span class="token comment">// 1. 导入 mysql 模块</span>
<span class="token keyword">var</span> mysql <span class="token operator">=</span> <span class="token function">require</span><span class="token punctuation">(</span><span class="token string">&quot;mysql&quot;</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token comment">// 2. 建立与 mysql 数据库的连接</span>
<span class="token keyword">var</span> db <span class="token operator">=</span> mysql<span class="token punctuation">.</span><span class="token function">createPool</span><span class="token punctuation">(</span><span class="token punctuation">{</span>
  host<span class="token operator">:</span> <span class="token string">&quot;127.0.0.1&quot;</span><span class="token punctuation">,</span> <span class="token comment">// 数据库的 ip 地址</span>
  user<span class="token operator">:</span> <span class="token string">&quot;root&quot;</span><span class="token punctuation">,</span> <span class="token comment">// 登录数据库的账号</span>
  password<span class="token operator">:</span> <span class="token string">&quot;toor&quot;</span><span class="token punctuation">,</span> <span class="token comment">// 登录数据库的密码</span>
  database<span class="token operator">:</span> <span class="token string">&quot;my_db_01&quot;</span><span class="token punctuation">,</span> <span class="token comment">// 指定要操作哪个数据库</span>
<span class="token punctuation">}</span><span class="token punctuation">)</span><span class="token punctuation">;</span>

<span class="token comment">// 3. 声明要插入到 users 表中的数据对象</span>
<span class="token keyword">const</span> user <span class="token operator">=</span> <span class="token punctuation">{</span> id<span class="token operator">:</span> <span class="token number">7</span><span class="token punctuation">,</span> username<span class="token operator">:</span> <span class="token string">&quot;spike&quot;</span><span class="token punctuation">,</span> password<span class="token operator">:</span> <span class="token string">&quot;123000&quot;</span> <span class="token punctuation">}</span><span class="token punctuation">;</span>
<span class="token comment">// 4. 待执行的 sql 语句</span>
<span class="token keyword">const</span> sqlstr <span class="token operator">=</span> <span class="token string">&quot;update users set ? where id=?&quot;</span><span class="token punctuation">;</span>

<span class="token comment">// 5. 调用 db.query() 执行 SQL 语句的同时，使用数组依次为占位符指定具体的值</span>
db<span class="token punctuation">.</span><span class="token function">query</span><span class="token punctuation">(</span>sqlstr<span class="token punctuation">,</span> <span class="token punctuation">[</span>user<span class="token punctuation">,</span> user<span class="token punctuation">.</span>id<span class="token punctuation">]</span><span class="token punctuation">,</span> <span class="token punctuation">(</span><span class="token parameter">err<span class="token punctuation">,</span> results</span><span class="token punctuation">)</span> <span class="token operator">=&gt;</span> <span class="token punctuation">{</span>
  <span class="token comment">// 更新失败</span>
  <span class="token keyword">if</span> <span class="token punctuation">(</span>err<span class="token punctuation">)</span> <span class="token keyword">return</span> console<span class="token punctuation">.</span><span class="token function">log</span><span class="token punctuation">(</span>err<span class="token punctuation">.</span>message<span class="token punctuation">)</span><span class="token punctuation">;</span>

  <span class="token comment">// 更新成功</span>
  <span class="token keyword">if</span> <span class="token punctuation">(</span>results<span class="token punctuation">.</span>affectedRows <span class="token operator">===</span> <span class="token number">1</span><span class="token punctuation">)</span> <span class="token punctuation">{</span>
    console<span class="token punctuation">.</span><span class="token function">log</span><span class="token punctuation">(</span><span class="token string">&quot;更新数据成功&quot;</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
  <span class="token punctuation">}</span>
<span class="token punctuation">}</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
</code></pre></div><h4 id="_6-10-使用-delete-语句删除数据"><a href="#_6-10-使用-delete-语句删除数据" class="header-anchor">#</a> 6.10 使用 delete 语句删除数据</h4> <ol><li><p>在删除数据时，推荐根据 <code>id</code> 这样的唯一标识，来删除对应的数据</p></li> <li><p><strong>注意：如果 sql 语句中有多个占位符，则必须使用数据为每个占位符指定具体的值，如果 sql 语句中只有一个占位符，则可以省略</strong></p></li> <li><p>案例代码</p></li></ol> <div class="language-js extra-class"><pre class="language-js"><code><span class="token comment">// 1. 导入 mysql 模块</span>
<span class="token keyword">var</span> mysql <span class="token operator">=</span> <span class="token function">require</span><span class="token punctuation">(</span><span class="token string">&quot;mysql&quot;</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token comment">// 2. 建立与 mysql 数据库的连接</span>
<span class="token keyword">var</span> db <span class="token operator">=</span> mysql<span class="token punctuation">.</span><span class="token function">createPool</span><span class="token punctuation">(</span><span class="token punctuation">{</span>
  host<span class="token operator">:</span> <span class="token string">&quot;127.0.0.1&quot;</span><span class="token punctuation">,</span> <span class="token comment">// 数据库的 ip 地址</span>
  user<span class="token operator">:</span> <span class="token string">&quot;root&quot;</span><span class="token punctuation">,</span> <span class="token comment">// 登录数据库的账号</span>
  password<span class="token operator">:</span> <span class="token string">&quot;toor&quot;</span><span class="token punctuation">,</span> <span class="token comment">// 登录数据库的密码</span>
  database<span class="token operator">:</span> <span class="token string">&quot;my_db_01&quot;</span><span class="token punctuation">,</span> <span class="token comment">// 指定要操作哪个数据库</span>
<span class="token punctuation">}</span><span class="token punctuation">)</span><span class="token punctuation">;</span>

<span class="token comment">// 3. 要执行的 sql 语句</span>
<span class="token keyword">const</span> sqlstr <span class="token operator">=</span> <span class="token string">&quot;delete from users where id=?&quot;</span><span class="token punctuation">;</span>

<span class="token comment">// 2. 调用 db.query() 执行 SQL 语句的同时，为占位符指定具体的值</span>
<span class="token comment">// 注意：如果 sql 语句中有多个占位符，则必须使用数据为每个占位符指定具体的值</span>
<span class="token comment">// 如果 sql 语句中只有一个占位符，则可以省略</span>
db<span class="token punctuation">.</span><span class="token function">query</span><span class="token punctuation">(</span>sqlstr<span class="token punctuation">,</span> <span class="token number">7</span><span class="token punctuation">,</span> <span class="token punctuation">(</span><span class="token parameter">err<span class="token punctuation">,</span> results</span><span class="token punctuation">)</span> <span class="token operator">=&gt;</span> <span class="token punctuation">{</span>
  <span class="token comment">// 删除失败</span>
  <span class="token keyword">if</span> <span class="token punctuation">(</span>err<span class="token punctuation">)</span> <span class="token keyword">return</span> console<span class="token punctuation">.</span><span class="token function">log</span><span class="token punctuation">(</span>err<span class="token punctuation">.</span>message<span class="token punctuation">)</span><span class="token punctuation">;</span>

  <span class="token comment">// 删除成功</span>
  <span class="token keyword">if</span> <span class="token punctuation">(</span>results<span class="token punctuation">.</span>affectedRows <span class="token operator">===</span> <span class="token number">1</span><span class="token punctuation">)</span> <span class="token punctuation">{</span>
    console<span class="token punctuation">.</span><span class="token function">log</span><span class="token punctuation">(</span><span class="token string">&quot;删除数据成功&quot;</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
  <span class="token punctuation">}</span>
<span class="token punctuation">}</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
</code></pre></div><h4 id="_6-11-标记删除"><a href="#_6-11-标记删除" class="header-anchor">#</a> 6.11 标记删除</h4> <ol><li><p>使用 <code>DELETE</code> 语句，会把真正的把数据从表中删除掉</p></li> <li><p>为了保险起见，推荐使用<strong>标记删除</strong>的形式，来<strong>模拟删除的动作</strong></p> <ul><li>标记删除，就是在表中设置类似于 <strong>status</strong> 这样的<strong>状态字段</strong>，来<strong>标记</strong>当前这条数据是否被删除</li></ul></li> <li><p>当用户执行了删除的动作时，我们并没有执行 <code>DELETE</code> 语句把数据删除掉，而是执行了 <code>UPDATE</code> 语句，将这条数据对应
的 <code>status</code> 字段标记为删除即可</p></li> <li><p>案例代码</p></li></ol> <div class="language-js extra-class"><pre class="language-js"><code><span class="token comment">// 1. 导入 mysql 模块</span>
<span class="token keyword">var</span> mysql <span class="token operator">=</span> <span class="token function">require</span><span class="token punctuation">(</span><span class="token string">&quot;mysql&quot;</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token comment">// 2. 建立与 mysql 数据库的连接</span>
<span class="token keyword">var</span> db <span class="token operator">=</span> mysql<span class="token punctuation">.</span><span class="token function">createPool</span><span class="token punctuation">(</span><span class="token punctuation">{</span>
  host<span class="token operator">:</span> <span class="token string">&quot;127.0.0.1&quot;</span><span class="token punctuation">,</span> <span class="token comment">// 数据库的 ip 地址</span>
  user<span class="token operator">:</span> <span class="token string">&quot;root&quot;</span><span class="token punctuation">,</span> <span class="token comment">// 登录数据库的账号</span>
  password<span class="token operator">:</span> <span class="token string">&quot;toor&quot;</span><span class="token punctuation">,</span> <span class="token comment">// 登录数据库的密码</span>
  database<span class="token operator">:</span> <span class="token string">&quot;my_db_01&quot;</span><span class="token punctuation">,</span> <span class="token comment">// 指定要操作哪个数据库</span>
<span class="token punctuation">}</span><span class="token punctuation">)</span><span class="token punctuation">;</span>

<span class="token comment">// 3. 要执行的 sql 语句</span>
<span class="token keyword">const</span> sqlstr <span class="token operator">=</span> <span class="token string">&quot;update users set status=1 where id=?&quot;</span><span class="token punctuation">;</span>

<span class="token comment">// 2. 调用 db.query() 执行 SQL 语句的同时，为占位符指定具体的值</span>
db<span class="token punctuation">.</span><span class="token function">query</span><span class="token punctuation">(</span>sqlstr<span class="token punctuation">,</span> <span class="token number">1</span><span class="token punctuation">,</span> <span class="token punctuation">(</span><span class="token parameter">err<span class="token punctuation">,</span> results</span><span class="token punctuation">)</span> <span class="token operator">=&gt;</span> <span class="token punctuation">{</span>
  <span class="token comment">// 删除失败</span>
  <span class="token keyword">if</span> <span class="token punctuation">(</span>err<span class="token punctuation">)</span> <span class="token keyword">return</span> console<span class="token punctuation">.</span><span class="token function">log</span><span class="token punctuation">(</span>err<span class="token punctuation">.</span>message<span class="token punctuation">)</span><span class="token punctuation">;</span>

  <span class="token comment">// 删除成功</span>
  <span class="token keyword">if</span> <span class="token punctuation">(</span>results<span class="token punctuation">.</span>affectedRows <span class="token operator">===</span> <span class="token number">1</span><span class="token punctuation">)</span> <span class="token punctuation">{</span>
    console<span class="token punctuation">.</span><span class="token function">log</span><span class="token punctuation">(</span><span class="token string">&quot;删除数据成功&quot;</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
  <span class="token punctuation">}</span>
<span class="token punctuation">}</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
</code></pre></div></div> <footer class="page-edit"><!----> <!----></footer> <div class="page-nav"><p class="inner"><span class="prev">
      ←
      <a href="/heima2020/node/day04.html" class="prev">
        Node 第 4 天
      </a></span> <span class="next"><a href="/heima2020/node/day06.html">
        Node 第 6 天
      </a>
      →
    </span></p></div> </main></div><div class="global-ui"><!----></div></div>
    <script src="/heima2020/assets/js/app.e2adc9d2.js" defer></script><script src="/heima2020/assets/js/2.7389c9d3.js" defer></script><script src="/heima2020/assets/js/9.cd5538bb.js" defer></script>
  </body>
</html>
